using Dapper;
using MediatR;
using RBAC.ErrorCode;
using RBAC.Interstructrue.Base;
using RBAC.Read.Api.Application.Command.Dashboard;
using RBAC.Read.Api.Application.Dto.Dashboard;

namespace RBAC.Read.Api.Application.Handler.Dashboard
{
    public class GetPendingApprovalCountsHandler : IRequestHandler<GetPendingApprovalCountsCommand, APIResult<PendingApprovalCountsDto>>
    {
        private readonly IDbConnectionFactory _db;
        public GetPendingApprovalCountsHandler(IDbConnectionFactory db)
        {
            _db = db;
        }

        public async Task<APIResult<PendingApprovalCountsDto>> Handle(GetPendingApprovalCountsCommand request, CancellationToken cancellationToken)
        {
            try
            {
                using var conn = _db.CreateConnection();
                // 统一使用“开立”作为待审批；部分表存在数值型Status=0 也视作待审批
                var sql = @"
SELECT COUNT(*) FROM PurchaseOrderInfo WHERE IsDeleted = 0 AND (docStatus = '开立' OR docStatus = '草稿');
SELECT COUNT(*) FROM SalesOrder          WHERE IsDeleted = 0 AND (OrderStatus = '开立');
SELECT COUNT(*) FROM ReturnNotice        WHERE IsDeleted = 0 AND ((BillStatus = '开立') OR (Status = 0));
SELECT COUNT(*) FROM PurchaseStorage     WHERE IsDeleted = 0 AND (OrderStatus = '开立');
SELECT COUNT(*) FROM SalesOutbound       WHERE IsDeleted = 0 AND (OrderStatus = '开立');
SELECT COUNT(*) FROM BillingAccounting   WHERE IsDeleted = 0 AND (BillStatus = '开立' OR Status = 0);
";
                // 目前未找到请购表，置为0

                using var multi = await conn.QueryMultipleAsync(sql);
                var dto = new PendingApprovalCountsDto
                {
                    PurchaseOrders = (await multi.ReadFirstOrDefaultAsync<int?>()) ?? 0,
                    SalesOrders    = (await multi.ReadFirstOrDefaultAsync<int?>()) ?? 0,
                    ReturnNotices  = (await multi.ReadFirstOrDefaultAsync<int?>()) ?? 0,
                    InboundOrders  = (await multi.ReadFirstOrDefaultAsync<int?>()) ?? 0,
                    OutboundOrders = (await multi.ReadFirstOrDefaultAsync<int?>()) ?? 0,
                    BillingNotices = (await multi.ReadFirstOrDefaultAsync<int?>()) ?? 0,
                    Requisitions   = 0
                };

                return new APIResult<PendingApprovalCountsDto>
                {
                    Code = APIEnums.Success,
                    Msg  = "查询成功",
                    Data = dto
                };
            }
            catch (Exception ex)
            {
                return new APIResult<PendingApprovalCountsDto>
                {
                    Code = APIEnums.Error,
                    Msg  = $"查询失败: {ex.Message}"
                };
            }
        }
    }

    public class GetMonthlyOverviewHandler : IRequestHandler<GetMonthlyOverviewCommand, APIResult<MonthlyOverviewDto>>
    {
        private readonly IDbConnectionFactory _db;
        public GetMonthlyOverviewHandler(IDbConnectionFactory db)
        {
            _db = db;
        }

        public async Task<APIResult<MonthlyOverviewDto>> Handle(GetMonthlyOverviewCommand request, CancellationToken cancellationToken)
        {
            try
            {
                var monthDate = request.Month?.Date ?? DateTime.Today;
                var firstDay = new DateTime(monthDate.Year, monthDate.Month, 1);
                var nextMonthFirst = firstDay.AddMonths(1);
                var today = DateTime.Today;

                using var conn = _db.CreateConnection();

                var sql = @"
-- 本月销售收入（以审核通过的开票明细金额为准）
SELECT IFNULL(SUM(bm.Amount),0) AS ThisMonthSalesAmount
FROM BillingMaterials bm
JOIN BillingAccounting ba ON bm.BillingAccountingId = ba.Id
WHERE bm.IsDeleted = 0 AND ba.IsDeleted = 0
  AND (ba.Status = 1 OR ba.BillStatus = '审核')
  AND ba.BillDate >= @FirstDay AND ba.BillDate < @NextMonthFirst;

-- 本月订单数（已审批的销售订单）
SELECT COUNT(*) AS ThisMonthOrderCount
FROM SalesOrder so
WHERE so.IsDeleted = 0 AND so.OrderStatus = '已审批'
  AND so.OrderDate IS NOT NULL AND so.OrderDate >= @FirstDay AND so.OrderDate < @NextMonthFirst;

-- 今日回款（以今日开票金额代替，如有回款表可替换）
SELECT IFNULL(SUM(bm.Amount),0) AS TodayCollectionAmount
FROM BillingMaterials bm
JOIN BillingAccounting ba ON bm.BillingAccountingId = ba.Id
WHERE bm.IsDeleted = 0 AND ba.IsDeleted = 0
  AND (ba.Status = 1 OR ba.BillStatus = '审核')
  AND DATE(ba.BillDate) = DATE(@Today);

-- 本月采购金额（采购发票物料金额合计，字符串转decimal）
SELECT IFNULL(SUM(CAST(pm.Amount AS DECIMAL(18,2))),0) AS ThisMonthPurchaseAmount
FROM ProcurementMaterials pm
JOIN PurchaseInvoice pi ON pm.PurchaseInvoiceId = pi.Id
WHERE pi.IsDeleted = 0 AND pm.IsDel = 0
  AND pi.InvoiceDate >= @FirstDay AND pi.InvoiceDate < @NextMonthFirst;

-- 本月发货数量（销售出库明细数量合计）
SELECT IFNULL(SUM(IFNULL(sod.Num,0)),0) AS ThisMonthShipmentQty
FROM SalesOutbound so
JOIN SalesOutboundDetail sod ON sod.SalesOutboundId = so.Id
WHERE so.IsDeleted = 0 AND sod.IsDeleted = 0
  AND so.OrderDate IS NOT NULL AND so.OrderDate >= @FirstDay AND so.OrderDate < @NextMonthFirst;
";

                using var multi = await conn.QueryMultipleAsync(sql, new
                {
                    FirstDay = firstDay,
                    NextMonthFirst = nextMonthFirst,
                    Today = today
                });

                var dto = new MonthlyOverviewDto
                {
                    ThisMonthSalesAmount    = await multi.ReadFirstOrDefaultAsync<decimal>(),
                    ThisMonthOrderCount     = await multi.ReadFirstOrDefaultAsync<int>(),
                    TodayCollectionAmount   = await multi.ReadFirstOrDefaultAsync<decimal>(),
                    ThisMonthPurchaseAmount = await multi.ReadFirstOrDefaultAsync<decimal>(),
                    ThisMonthShipmentQty    = await multi.ReadFirstOrDefaultAsync<int>(),
                    ThisMonthInventoryAmount = 0m
                };

                return new APIResult<MonthlyOverviewDto>
                {
                    Code = APIEnums.Success,
                    Msg = "查询成功",
                    Data = dto
                };
            }
            catch (Exception ex)
            {
                return new APIResult<MonthlyOverviewDto>
                {
                    Code = APIEnums.Error,
                    Msg = $"统计失败: {ex.Message}"
                };
            }
        }
    }
}
